
# 同合并报表名称和数据项，TF-IDF向量化文本，计算余弦相似度排序

import pandas as pd
from sklearn.feature_extraction.text import TfidfVectorizer
from sklearn.metrics.pairwise import cosine_similarity
import numpy as np
import warnings

warnings.filterwarnings('ignore')


def calculate_similarity(texts):
    """
    计算文本列表的相似度矩阵
    """
    if len(texts) <= 1:
        return np.array([[1.0]] if texts else [])

    # 使用TF-IDF向量化文本
    vectorizer = TfidfVectorizer()
    tfidf_matrix = vectorizer.fit_transform(texts)

    # 计算余弦相似度
    similarity_matrix = cosine_similarity(tfidf_matrix)
    return similarity_matrix


def process_excel_file(file_path,sheet_name):
    """
    处理Excel文件：按部门分组并计算相似度
    """
    # 读取Excel文件
    # self.df = pd.read_excel(self.file_path, sheet_name=self.sheet_name)
    df = pd.read_excel(file_path,sheet_name = sheet_name)

    # 确保必要的列存在
    required_columns = ['部门名称', '报表名称', '数据项']
    for col in required_columns:
        if col not in df.columns:
            raise ValueError(f"Excel文件中缺少必需的列: {col}")

    # 按部门分组
    grouped = df.groupby('部门名称')

    results = []

    for department, group in grouped:
        # 获取当前部门的记录
        records = group.to_dict('records')

        # 为每条记录创建合并文本
        texts = [f"{record['报表名称']} {record['数据项']}" for record in records]

        # 计算相似度矩阵
        similarity_matrix = calculate_similarity(texts)

        # 计算每条记录与其他记录的平均相似度
        avg_similarities = []
        for i in range(len(records)):
            if len(similarity_matrix) > 0:
                # 排除与自身的比较（相似度为1）
                other_indices = [j for j in range(len(similarity_matrix[i])) if j != i]
                if other_indices:
                    avg_sim = np.mean(similarity_matrix[i, other_indices])
                else:
                    avg_sim = 1.0  # 只有一条记录时
            else:
                avg_sim = 1.0
            avg_similarities.append(avg_sim)

        # 添加相似度到记录中
        for i, record in enumerate(records):
            record['相似度'] = avg_similarities[i]

        # 按相似度排序
        sorted_records = sorted(records, key=lambda x: x['相似度'], reverse=True)

        # 添加部门信息
        for record in sorted_records:
            record['部门'] = department
            results.append(record)

    # 创建结果DataFrame
    result_df = pd.DataFrame(results)

    # 重新排列列的顺序
    cols = ['部门', '报表名称', '数据项', '相似度'] + [col for col in result_df.columns if
                                                       col not in ['部门', '报表名称', '数据项', '相似度']]
    result_df = result_df[cols]

    return result_df


# 使用示例
if __name__ == "__main__":
    # 替换为你的Excel文件路径
    excel_file_path = "C:/Users/xingwenzheng/Desktop/国家部委-附件1-基层报表底数初步清单-1.xlsx"
    sheet_name = "分割前数据"

    try:
        result = process_excel_file(excel_file_path,sheet_name)

        # 打印结果
        print("相似度分析结果:")
        print(result.to_string(index=False))

        # 保存结果到新的Excel文件
        output_path = "C:/Users/xingwenzheng/Desktop/相似度分析结果.xlsx"
        result.to_excel(output_path, index=False)
        print(f"\n结果已保存到: {output_path}")

    except Exception as e:
        print(f"处理过程中发生错误: {e}")

